// ====================================
// 数据库初始化脚本 (Dart)
// 用于Flutter应用中快速创建和初始化SQLite数据库
// ====================================

import 'dart:io';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

class DatabaseInitializer {
  static const String _databaseName = 'our_little_universe.db';
  static const int _databaseVersion = 1;
  
  static Database? _database;
  
  /// 获取数据库实例 (单例模式)
  static Future<Database> get database async {
    _database ??= await _initDatabase();
    return _database!;
  }
  
  /// 初始化数据库
  static Future<Database> _initDatabase() async {
    String path = join(await getDatabasesPath(), _databaseName);
    
    return await openDatabase(
      path,
      version: _databaseVersion,
      onCreate: _createDatabase,
      onConfigure: _onConfigure,
    );
  }
  
  /// 配置数据库
  static Future<void> _onConfigure(Database db) async {
    // 启用外键约束
    await db.execute('PRAGMA foreign_keys = ON');
    // 启用WAL模式提升性能
    await db.execute('PRAGMA journal_mode = WAL');
    // 设置同步模式
    await db.execute('PRAGMA synchronous = NORMAL');
  }
  
  /// 创建数据库表结构
  static Future<void> _createDatabase(Database db, int version) async {
    // 按模块顺序创建表
    await _createHomeModule(db);
    await _createUserModule(db);
    await _createTimelineModule(db);
    await _createImportantDaysModule(db);
    await _createIntegrationConstraints(db);
    await _insertInitialData(db);
    
    print('✅ 数据库初始化完成！');
  }
  
  /// 1. 首页模块表创建
  static Future<void> _createHomeModule(Database db) async {
    // 情侣关系表
    await db.execute('''
      CREATE TABLE couples (
        id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
        partner1_name TEXT NOT NULL,
        partner2_name TEXT NOT NULL,
        relationship_start_date TEXT NOT NULL,
        anniversary_name TEXT DEFAULT '纪念日',
        couple_avatar_emoji TEXT DEFAULT '👩‍❤️‍👨',
        relationship_status TEXT DEFAULT 'active',
        timezone TEXT DEFAULT 'Asia/Shanghai',
        created_at INTEGER DEFAULT (strftime('%s', 'now')),
        updated_at INTEGER DEFAULT (strftime('%s', 'now'))
      )
    ''');
    
    // 每日心情表
    await db.execute('''
      CREATE TABLE daily_moods (
        id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
        user_id TEXT NOT NULL,
        mood_type TEXT NOT NULL,
        mood_date TEXT NOT NULL,
        mood_note TEXT,
        mood_intensity INTEGER DEFAULT 5,
        shared_with_partner INTEGER DEFAULT 1,
        created_at INTEGER DEFAULT (strftime('%s', 'now')),
        updated_at INTEGER DEFAULT (strftime('%s', 'now')),
        UNIQUE(user_id, mood_date)
      )
    ''');
    
    // 天气设置表
    await db.execute('''
      CREATE TABLE weather_settings (
        id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
        user_id TEXT NOT NULL,
        city_name TEXT NOT NULL,
        city_code TEXT,
        latitude REAL,
        longitude REAL,
        auto_location INTEGER DEFAULT 1,
        weather_provider TEXT DEFAULT 'qweather',
        show_air_quality INTEGER DEFAULT 1,
        show_humidity INTEGER DEFAULT 1,
        temperature_unit TEXT DEFAULT 'celsius',
        created_at INTEGER DEFAULT (strftime('%s', 'now')),
        updated_at INTEGER DEFAULT (strftime('%s', 'now')),
        UNIQUE(user_id)
      )
    ''');
    
    // 天气缓存表
    await db.execute('''
      CREATE TABLE weather_cache (
        id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
        user_id TEXT NOT NULL,
        city_code TEXT NOT NULL,
        temperature REAL,
        weather_icon TEXT,
        weather_description TEXT,
        humidity INTEGER,
        cached_at INTEGER DEFAULT (strftime('%s', 'now')),
        expires_at INTEGER,
        UNIQUE(user_id, city_code)
      )
    ''');
    
    // 情话表
    await db.execute('''
      CREATE TABLE love_quotes (
        id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
        content TEXT NOT NULL,
        category TEXT DEFAULT 'general',
        language TEXT DEFAULT 'zh-CN',
        mood_tags TEXT DEFAULT '[]',
        is_active INTEGER DEFAULT 1,
        created_at INTEGER DEFAULT (strftime('%s', 'now'))
      )
    ''');
    
    // 系统配置表
    await db.execute('''
      CREATE TABLE system_configs (
        id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
        config_key TEXT NOT NULL UNIQUE,
        config_value TEXT,
        description TEXT,
        is_public INTEGER DEFAULT 0,
        created_at INTEGER DEFAULT (strftime('%s', 'now')),
        updated_at INTEGER DEFAULT (strftime('%s', 'now'))
      )
    ''');
    
    print('📱 首页模块表创建完成');
  }
  
  /// 2. 用户模块表创建
  static Future<void> _createUserModule(Database db) async {
    // 用户表
    await db.execute('''
      CREATE TABLE users (
        id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
        couple_id TEXT NOT NULL,
        name TEXT NOT NULL,
        avatar_emoji TEXT DEFAULT '👩',
        role TEXT NOT NULL CHECK (role IN ('partner1', 'partner2')),
        email TEXT,
        birthday TEXT,
        bio TEXT,
        preferences TEXT DEFAULT '{}',
        last_active_at INTEGER,
        is_active INTEGER DEFAULT 1,
        created_at INTEGER DEFAULT (strftime('%s', 'now')),
        updated_at INTEGER DEFAULT (strftime('%s', 'now'))
      )
    ''');
    
    // 用户会话表
    await db.execute('''
      CREATE TABLE user_sessions (
        id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
        user_id TEXT NOT NULL,
        session_token TEXT NOT NULL UNIQUE,
        device_type TEXT,
        expires_at INTEGER NOT NULL,
        last_used_at INTEGER DEFAULT (strftime('%s', 'now')),
        created_at INTEGER DEFAULT (strftime('%s', 'now'))
      )
    ''');
    
    print('👤 用户模块表创建完成');
  }
  
  /// 3. 时光轴模块表创建
  static Future<void> _createTimelineModule(Database db) async {
    // 回忆表
    await db.execute('''
      CREATE TABLE memories (
        id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
        user_id TEXT NOT NULL,
        title TEXT NOT NULL,
        description TEXT,
        content TEXT,
        memory_date TEXT NOT NULL,
        location_name TEXT,
        image_paths TEXT DEFAULT '[]',
        image_count INTEGER DEFAULT 0,
        mood_type TEXT,
        tags TEXT DEFAULT '[]',
        is_favorite INTEGER DEFAULT 0,
        weather_snapshot_id TEXT,
        created_at INTEGER DEFAULT (strftime('%s', 'now')),
        updated_at INTEGER DEFAULT (strftime('%s', 'now'))
      )
    ''');
    
    // 时光轴事件表
    await db.execute('''
      CREATE TABLE timeline_events (
        id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
        user_id TEXT NOT NULL,
        title TEXT NOT NULL,
        description TEXT,
        event_date TEXT NOT NULL,
        category TEXT NOT NULL,
        emoji TEXT NOT NULL DEFAULT '📝',
        image_paths TEXT DEFAULT '[]',
        importance_level INTEGER DEFAULT 3,
        weather_snapshot_id TEXT,
        created_at INTEGER DEFAULT (strftime('%s', 'now')),
        updated_at INTEGER DEFAULT (strftime('%s', 'now'))
      )
    ''');
    
    // 天气快照表
    await db.execute('''
      CREATE TABLE weather_snapshots (
        id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
        memory_id TEXT,
        event_id TEXT,
        temperature REAL,
        weather_icon TEXT,
        weather_description TEXT,
        snapshot_date TEXT NOT NULL,
        created_at INTEGER DEFAULT (strftime('%s', 'now')),
        CHECK (memory_id IS NOT NULL OR event_id IS NOT NULL)
      )
    ''');
    
    print('📜 时光轴模块表创建完成');
  }
  
  /// 4. 重要日子模块表创建
  static Future<void> _createImportantDaysModule(Database db) async {
    await db.execute('''
      CREATE TABLE important_days (
        id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
        user_id TEXT NOT NULL,
        title TEXT NOT NULL,
        description TEXT,
        target_date TEXT NOT NULL,
        category TEXT NOT NULL,
        emoji TEXT NOT NULL DEFAULT '⭐',
        is_recurring INTEGER DEFAULT 0,
        reminder_days_before TEXT DEFAULT '[1,7,30]',
        is_read INTEGER DEFAULT 0,
        priority INTEGER DEFAULT 3,
        created_at INTEGER DEFAULT (strftime('%s', 'now')),
        updated_at INTEGER DEFAULT (strftime('%s', 'now'))
      )
    ''');
    
    print('⭐ 重要日子模块表创建完成');
  }
  
  /// 5. 创建索引和约束
  static Future<void> _createIntegrationConstraints(Database db) async {
    // 创建关键索引
    final indexes = [
      'CREATE INDEX idx_users_couple_id ON users(couple_id)',
      'CREATE INDEX idx_memories_user_date ON memories(user_id, memory_date DESC)',
      'CREATE INDEX idx_timeline_events_user_date ON timeline_events(user_id, event_date DESC)',
      'CREATE INDEX idx_important_days_date ON important_days(target_date)',
      'CREATE INDEX idx_daily_moods_user_date ON daily_moods(user_id, mood_date DESC)',
      'CREATE UNIQUE INDEX idx_users_couple_role ON users(couple_id, role)',
    ];
    
    for (String index in indexes) {
      await db.execute(index);
    }
    
    print('🔗 索引和约束创建完成');
  }
  
  /// 6. 插入初始数据
  static Future<void> _insertInitialData(Database db) async {
    // 插入默认系统配置
    await db.insert('system_configs', {
      'config_key': 'app_version',
      'config_value': '"1.0.0"',
      'description': '应用版本号',
      'is_public': 1,
    });
    
    await db.insert('system_configs', {
      'config_key': 'weather_cache_duration',
      'config_value': '3600',
      'description': '天气缓存时长(秒)',
      'is_public': 0,
    });
    
    // 插入默认情话
    final loveQuotes = [
      {'content': '愿我们的爱情永远甜蜜如初 💕', 'category': 'general'},
      {'content': '每一天和你在一起，都是最美的时光 ✨', 'category': 'morning'},
      {'content': '晚安，我的宝贝，愿你有个甜美的梦 🌙', 'category': 'evening'},
      {'content': '感谢命运让我遇见了你 🙏', 'category': 'anniversary'},
    ];
    
    for (Map<String, dynamic> quote in loveQuotes) {
      await db.insert('love_quotes', quote);
    }
    
    print('📝 初始数据插入完成');
  }
  
  /// 创建示例数据 (可选)
  static Future<void> createSampleData() async {
    final db = await database;
    
    // 创建示例情侣关系
    final coupleId = await db.insert('couples', {
      'id': 'sample_couple_001',
      'partner1_name': '小美',
      'partner2_name': '小强',
      'relationship_start_date': '2024-01-20',
    });
    
    // 创建示例用户
    await db.insert('users', {
      'id': 'user_001',
      'couple_id': 'sample_couple_001',
      'name': '小美',
      'role': 'partner1',
      'avatar_emoji': '👩',
      'birthday': '1995-05-15',
    });
    
    await db.insert('users', {
      'id': 'user_002', 
      'couple_id': 'sample_couple_001',
      'name': '小强',
      'role': 'partner2',
      'avatar_emoji': '👨',
      'birthday': '1993-08-22',
    });
    
    print('🎯 示例数据创建完成！');
  }
  
  /// 数据库维护 - 清理过期数据
  static Future<void> cleanupExpiredData() async {
    final db = await database;
    
    // 清理过期会话
    await db.delete(
      'user_sessions',
      where: 'expires_at < ?',
      whereArgs: [DateTime.now().millisecondsSinceEpoch ~/ 1000],
    );
    
    // 清理过期天气缓存
    await db.delete(
      'weather_cache',
      where: 'expires_at < ?', 
      whereArgs: [DateTime.now().millisecondsSinceEpoch ~/ 1000],
    );
    
    print('🧹 数据清理完成');
  }
  
  /// 获取数据库统计信息
  static Future<Map<String, int>> getDatabaseStats() async {
    final db = await database;
    
    final stats = <String, int>{};
    
    // 统计各表记录数
    final tables = [
      'couples', 'users', 'memories', 'timeline_events', 
      'important_days', 'daily_moods', 'love_quotes'
    ];
    
    for (String table in tables) {
      final result = await db.rawQuery('SELECT COUNT(*) as count FROM $table');
      stats[table] = result.first['count'] as int;
    }
    
    return stats;
  }
  
  /// 关闭数据库连接
  static Future<void> closeDatabase() async {
    if (_database != null) {
      await _database!.close();
      _database = null;
      print('🔒 数据库连接已关闭');
    }
  }
}

/// 使用示例
void main() async {
  print('🚀 开始初始化数据库...');
  
  // 初始化数据库
  final db = await DatabaseInitializer.database;
  print('✅ 数据库连接成功: ${db.path}');
  
  // 创建示例数据 (可选)
  await DatabaseInitializer.createSampleData();
  
  // 查看数据库统计
  final stats = await DatabaseInitializer.getDatabaseStats();
  print('📊 数据库统计: $stats');
  
  // 清理过期数据
  await DatabaseInitializer.cleanupExpiredData();
  
  print('🎉 数据库初始化完成！');
}
